const sql = require('mssql');
const baseFn = require('../../public/base.js');

/**
 * @description 入库拒收查询
 */



// 查询拒收单的订单信息
const getStorageRefuseOrder = async (ctx) => {
    try {

        const { index, pageSize, searchList } = ctx.request.body;

        let searchSqlList = [];
        if (searchList.GYS) {
            searchSqlList.push(` AND (DW.BM LIKE '%${searchList.GYS}%' OR DW.MC LIKE '%${searchList.GYS}%' OR DW.JP LIKE '%${searchList.GYS}%' ) `);
        }

        if (searchList.YPBM) {
            searchSqlList.push(` AND (Y.BM LIKE '%${searchList.YPBM}%' OR Y.MC LIKE '%${searchList.YPBM}%' OR Y.JP LIKE '%${searchList.YPBM}%' ) `);
        }
        if (searchList.RQ[0]) {
            searchSqlList.push(` AND JS.RQ >= '${searchList.RQ[0]} 00:00:00' AND JS.RQ <= '${searchList.RQ[1]} 23:59:59' `);
        }
        if (searchList.BMBM) {
            searchSqlList.push(` AND JS.BMBM = '${searchList.BMBM}' `);
        }
        let publicSql = `(SELECT ROW_NUMBER() OVER(ORDER BY JS.ID_JSD DESC) AS px,JS.RQ,JS.ID_JSD,JS.DWBM,JS.BMBM,JS.JSRMC,
    JS.CGYMC,JS.THFS,DW.MC AS DWMC,BM.MC AS BMMC,DW.DH FROM GSP_YPJSBGD JS LEFT JOIN BM_WLDW DW ON JS.DWBM = DW.BM LEFT JOIN BM_BM BM ON JS.BMBM = BM.BM LEFT JOIN BM_YP Y ON JS.YPBM = Y.BM
        WHERE 1=1 ${searchSqlList.join(' ')} GROUP BY JS.RQ,JS.ID_JSD,JS.DWBM,JS.BMBM,JS.JSRMC,JS.CGYMC,JS.THFS,DW.MC,BM.MC,DW.DH) `;

        let totalSql = ` SELECT COUNT(t1.ID_JSD) AS NUM from ${publicSql} AS t1 `;

        let selectSql = ` SELECT * FROM ${publicSql}  AS t1 WHERE t1.px between ${(index - 1) * pageSize + 1} and ${index * pageSize} `


        let totalResult = await sql.query(totalSql);

        let selectResult = await sql.query(selectSql);
        ctx.body = { code: 200, message: '获取数据成功', data: { total: totalResult.recordset[0].NUM, result: selectResult.recordset } }
    } catch (err) {
        ctx.body = { code: 500, message: err }
    }
}


// 查询拒收单的订单详情
const getStorageRefuseDetail = async (ctx) => {
    try {

        const { id } = ctx.request.body;
        let selectSql = ` SELECT  GS.ID, GS.BMBM, GS.BMMC, GS.YPBM, GS.TYMC, GS.YPMC, GS.JXMC, GS.YPGG, GS.PZWH, GS.SCCJ, GS.GJQY, GS.JLDW, GS.SL, 
    GS.SCPH, GS.YXQZ, GS.JSYY, GS.YJ1, GS.RQ1, GS.QZ1, GS.YJ2, GS.RQ2, GS.QZ2, GS.YJ3, GS.RQ3, GS.QZ3, GS.CZY, GS.DJ, GS.JE, GS.JSLX, GS.DWBM, 
    GS.CGYMC, GS.JSRMC, GS.THFS, GS.YFJS, GS.WLGS, GS.WLDH, GS.ID_JSD FROM GSP_YPJSBGD GS LEFT JOIN BM_WLDW DW ON GS.DWBM = DW.BM 
    LEFT JOIN BM_YP Y ON GS.YPBM = Y.BM WHERE  GS.ID_JSD = '${id}'  `;

        let selectResult = await sql.query(selectSql);

        if (selectResult.rowsAffected[0] > 0) {
            ctx.body = { code: 200, message: '获取数据成功', data: { result: selectResult.recordset } }
        } else {
            ctx.body = { code: 400, message: '获取数据失败' }
        }

    } catch (err) {
        ctx.body = { code: 500, message: err }
    }
}


// 查询要导出的订单详情
const getExportRefuseInfo = async (ctx) => {
    try {

        const { RQ, BMBM, GYS, YPBM } = ctx.request.body;
        let selectSql = ` SELECT JS.RQ,DW.MC AS DWMC,JS.YPBM,JS.YPMC,JS.YPGG,JS.PZWH,JS.SCCJ,JS.JLDW,JS.SL,JS.SCPH  FROM GSP_YPJSBGD JS 
        LEFT JOIN BM_WLDW DW ON JS.DWBM = DW.BM  LEFT JOIN BM_YP Y ON JS.YPBM = Y.BM WHERE 1=1  `;
        let searchSqlList = [];
        if (GYS) {
            searchSqlList.push(` AND (DW.BM LIKE '%${GYS}%' OR DW.MC LIKE '%${GYS}%' OR DW.JP LIKE '%${GYS}%' ) `);
        }

        if (YPBM) {
            searchSqlList.push(` AND (Y.BM LIKE '%${YPBM}%' OR Y.MC LIKE '%${YPBM}%' OR Y.JP LIKE '%${YPBM}%' ) `);
        }
        if (RQ[0]) {
            searchSqlList.push(` AND JS.RQ >= '${RQ[0]} 00:00:00' AND JS.RQ <= '${RQ[1]} 23:59:59' `);
        }
        if (BMBM) {
            searchSqlList.push(` AND JS.BMBM = '${BMBM}' `);
        }
        selectSql += searchSqlList.join(' ');
        let selectResult = await sql.query(selectSql);

        if (selectResult.rowsAffected[0] > 0) {
            ctx.body = { code: 200, message: '获取数据成功', data: { result: selectResult.recordset } }
        } else {
            ctx.body = { code: 400, message: '获取数据失败' }
        }

    } catch (err) {
        ctx.body = { code: 500, message: err }
    }
}





module.exports = {
    'POST /getStorageRefuseOrder': getStorageRefuseOrder,
    'POST /getExportRefuseInfo': getExportRefuseInfo,
    'POST /getStorageRefuseDetail': getStorageRefuseDetail
}